set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


insert overwrite table jms_dm.dm_waybill_fayun_late_mid_dt
select
     order_source_code           --订单来源编码
    ,order_source_name           --订单来源名称
    ,scan_site_code              --当前站点编码
    ,scan_site_name              --当前站点名称
    ,scan_site_city_code         --当前站点所属城市编码
    ,scan_site_city_name         --当前站点所属城市名称
    ,scan_site_provider_code     --当前站点所属省份编码
    ,scan_site_provider_name     --当前站点所属省份名称
    ,scan_site_agent_code        --当前站点所属代理区编码
    ,scan_site_agent_name        --当前站点所属代理区名称
    ,late_type                   --超时环节
    ,duty_type                   --异常类别
    ,duty_level                  --异常等级
    ,scale_level                 --规模等级
    ,case when duty_level = '正常'     then 0
          when duty_level = '一般异常' then 1
          when duty_level = '严重异常' then 2
          else 0 end as today_score            --今日积分
    ,oper_cnt                    --应操作量
    ,duty_cnt                    --异常量
    ,duty_rate                   --异常率
    ,peak_cnt                    --峰值件量
    ,ydlf_36h_cnt                --有到漏发超36h量
    ,yfwx_36h_cnt                --有发未卸超36h量
    ,dt
from (
    select
         order_source_code        --订单来源编码
        ,order_source_name        --订单来源名称
        ,scan_site_code           --当前站点编码
        ,scan_site_name           --当前站点名称
        ,scan_site_city_code      --当前站点所属城市编码
        ,scan_site_city_name      --当前站点所属城市名称
        ,scan_site_provider_code  --当前站点所属省份编码
        ,scan_site_provider_name  --当前站点所属省份名称
        ,scan_site_agent_code     --当前站点所属代理区编码
        ,scan_site_agent_name     --当前站点所属代理区名称
        ,late_type                --超时环节
        ,duty_type                --异常环节
        ,oper_cnt                  --应操作量
        ,duty_cnt                  --异常量
        ,ydlf_36h_cnt              --有到漏发超36h量
        ,yfwx_36h_cnt              --有发未卸超36h量
        ,peak_cnt                  --峰值件量
        ,scale_level               --规模等级
        ,duty_rate
        ,case when duty_type in ('发运超时36H' ,'首中心停滞36H','【网点】干线停滞+1D') then
              case when scale_level = '极小' then '正常'
                   when duty_rate >= 10 or duty_cnt >= 2000 then '严重异常'
                   when (duty_rate >= 4 and duty_rate < 10) or duty_cnt >= 800 then '一般异常'
                   when duty_rate >= 0  and duty_rate < 4 then '正常'
                   end
              when duty_type in ('中心停滞36H' ,'【中心】干线停滞+1D','支线停滞36H') then
                   case when scale_level = '极小' then '正常'
                        when (scale_level = '小' and duty_rate >= 0 and duty_rate < 1)
                          or (scale_level = '中' and duty_rate >= 0 and duty_rate < 0.5)
                          or (scale_level = '大' and duty_rate >= 0 and duty_rate < 0.1)
                        then '正常'
                        when (scale_level = '小' and duty_rate >= 1   and duty_rate < 10)
                          or (scale_level = '中' and duty_rate >= 0.5 and duty_rate < 1)
                          or (scale_level = '大' and duty_rate >= 0.1 and duty_rate < 0.5)
                        then '一般异常'
                        when (scale_level = '小' and duty_rate >= 10)
                          or (scale_level = '中' and duty_rate >= 1)
                          or (scale_level = '大' and (duty_rate >= 0.5) or (duty_cnt >= 10000))
                        then '严重异常'
                    end
              when duty_type in ('超时未派24H' ,'派签超时24H') then
                   case when scale_level = '极小' then '正常'
                        when duty_rate >= 0 and duty_rate < 5 then '正常'
                        when (scale_level = '小' and duty_rate >= 5 and duty_rate < 12)
                          or (scale_level = '中' and duty_rate >= 5 and duty_rate < 10)
                          or (scale_level = '大' and ((duty_rate >= 5 and duty_rate < 10) or (duty_cnt >= 100)))
                        then '一般异常'
                        when (scale_level = '小' and duty_rate >= 12)
                          or (scale_level = '中' and duty_rate >= 10)
                          or (scale_level = '大' and (duty_rate >= 10 or duty_cnt >= 500))
                        then '严重异常'
                    end
             end as duty_level --异常等级
        ,dt
    from (
        select
             base.order_source_code       as order_source_code        --订单来源编码
            ,base.order_source_name       as order_source_name        --订单来源名称
            ,base.scan_site_code          as scan_site_code           --当前站点编码
            ,base.scan_site_name          as scan_site_name           --当前站点名称
            ,base.scan_site_city_code     as scan_site_city_code      --当前站点所属城市编码
            ,base.scan_site_city_name     as scan_site_city_name      --当前站点所属城市名称
            ,base.scan_site_provider_code as scan_site_provider_code  --当前站点所属省份编码
            ,base.scan_site_provider_name as scan_site_provider_name  --当前站点所属省份名称
            ,base.scan_site_agent_code    as scan_site_agent_code     --当前站点所属代理区编码
            ,base.scan_site_agent_name    as scan_site_agent_name     --当前站点所属代理区名称
            ,base.late_type               as late_type                --超时环节
            ,base.duty_type               as duty_type                --异常环节
            ,base.oper_cnt                as oper_cnt                  --应操作量
            ,base.duty_cnt                as duty_cnt                  --异常量
            ,base.ydlf_36h_cnt            as ydlf_36h_cnt              --有到漏发超36h量
            ,base.yfwx_36h_cnt            as yfwx_36h_cnt              --有发未卸超36h量
            ,peak.peak_cnt                as peak_cnt                  --峰值件量
            ,case when base.duty_type in ('发运超时36H' ,'首中心停滞36H','【网点】干线停滞+1D') then
                  case when peak.peak_cnt >= 0   and peak.peak_cnt < 100    then '极小'
                       when peak.peak_cnt >= 100 and peak.peak_cnt <= 5000  then '小'
                       when peak.peak_cnt > 5000 and peak.peak_cnt <= 20000 then '中'
                       when peak.peak_cnt > 20000 then '大'
                       end
                  when base.duty_type in ('中心停滞36H' ,'【中心】干线停滞+1D','支线停滞36H') then
                  case when peak.peak_cnt >= 0    and peak.peak_cnt < 1000    then '极小'
                       when peak.peak_cnt >= 1000 and peak.peak_cnt <= 50000  then '小'
                       when peak.peak_cnt > 50000 and peak.peak_cnt <= 2000000 then '中'
                       when peak.peak_cnt > 200000 then '大'
                       end
                  when base.duty_type in ('超时未派24H' ,'派签超时24H') then
                  case when peak.peak_cnt >= 0   and peak.peak_cnt <  100 then '极小'
                       when peak.peak_cnt >= 100 and peak.peak_cnt <= 500 then '小'
                       when peak.peak_cnt > 500  and peak.peak_cnt <= 2000 then '中'
                       when peak.peak_cnt > 2000 then '大'
                       end
                  end as scale_level  --规模等级
            ,round(base.duty_cnt / base.oper_cnt,4) * 100 as duty_rate --异常率
            ,base.dt
        from (
            select
                 base.order_source_code            --订单来源编码
                ,base.order_source_name            --订单来源名称
                ,base.scan_site_code               --当前站点编码
                ,base.scan_site_name               --当前站点名称
                ,base.scan_site_city_code          --当前站点所属城市编码
                ,base.scan_site_city_name          --当前站点所属城市名称
                ,base.scan_site_provider_code      --当前站点所属省份编码
                ,base.scan_site_provider_name      --当前站点所属省份名称
                ,base.scan_site_agent_code         --当前站点所属代理区编码
                ,base.scan_site_agent_name         --当前站点所属代理区名称
                ,base.late_type                    --超时环节
                ,base.duty_type                    --异常环节
                ,base.type
                ,oper.oper_cnt     as oper_cnt     --应操作量
                ,base.duty_cnt     as duty_cnt     --异常量
                ,base.ydlf_36h_cnt as ydlf_36h_cnt --有到漏发超36h量
                ,base.yfwx_36h_cnt as yfwx_36h_cnt --有发未卸超36h量
                ,base.dt
            from (
                select
                     order_source_code        --订单来源编码
                    ,order_source_name        --订单来源名称
                    ,scan_site_code           --当前站点编码
                    ,scan_site_name           --当前站点名称
                    ,scan_site_city_code      --当前站点所属城市编码
                    ,scan_site_city_name      --当前站点所属城市名称
                    ,scan_site_provider_code  --当前站点所属省份编码
                    ,scan_site_provider_name  --当前站点所属省份名称
                    ,scan_site_agent_code     --当前站点所属代理区编码
                    ,scan_site_agent_name     --当前站点所属代理区名称
                    ,late_type                --超时环节
                    ,duty_type                --异常环节
                    ,type
                    ,count(1)          as duty_cnt     --异常量
                    ,sum(if_ydlf_36h)  as ydlf_36h_cnt --有到漏发超36h量
                    ,sum(if_yfwx_36h)  as yfwx_36h_cnt --有发未卸超36h量
                    ,dt
                from jms_dm.dm_waybill_fayun_late_detail_dt
                where dt = '{{ execution_date | cst_ds }}'
                and next_time > oper_time --只保留异常数据
                and duty_type is not null
                group by order_source_code        --订单来源编码
                        ,order_source_name        --订单来源名称
                        ,scan_site_code           --当前站点编码
                        ,scan_site_name           --当前站点名称
                        ,scan_site_city_code      --当前站点所属城市编码
                        ,scan_site_city_name      --当前站点所属城市名称
                        ,scan_site_provider_code  --当前站点所属省份编码
                        ,scan_site_provider_name  --当前站点所属省份名称
                        ,scan_site_agent_code     --当前站点所属代理区编码
                        ,scan_site_agent_name     --当前站点所属代理区名称
                        ,late_type                --超时环节
                        ,duty_type                --异常环节
                        ,type
                        ,dt
            ) base
            left join (
                select
                     order_source_code        --订单来源编码
                    ,order_source_name        --订单来源名称
                    ,scan_site_code           --当前站点编码
                    ,scan_site_name           --当前站点名称
                    ,type
                    ,count(1) as oper_cnt     --异常量
                from jms_dm.dm_waybill_fayun_late_detail_dt
                where dt = '{{ execution_date | cst_ds }}'
                group by order_source_code        --订单来源编码
                        ,order_source_name        --订单来源名称
                        ,scan_site_code           --当前站点编码
                        ,scan_site_name           --当前站点名称
                        ,type
            ) oper on base.order_source_code = oper.order_source_code --订单来源编码
                  and base.order_source_name = oper.order_source_name --订单来源名称
                  and base.scan_site_code    = oper.scan_site_code    --当前站点编码
                  and base.scan_site_name    = oper.scan_site_name    --当前站点名称
                  and base.type              = oper.type    --当前站点名称
        ) base
        left join (
            select
                 order_source_code          --订单来源编码
                ,order_source_name          --订单来源名称
                ,scan_site_code             --当前站点编码
                ,scan_site_name             --当前站点名称
                ,type
                ,max(oper_cnt) as peak_cnt  --峰值件量
            from (
                select
                     order_source_code        --订单来源编码
                    ,order_source_name        --订单来源名称
                    ,scan_site_code           --当前站点编码
                    ,scan_site_name           --当前站点名称
                    ,type
                    ,count(1) as oper_cnt     --应操作量
                    ,dt
                from jms_dm.dm_waybill_fayun_late_detail_dt
                where dt between date_sub('{{ execution_date | cst_ds }}',30) and '{{ execution_date | cst_ds }}'
                group by order_source_code        --订单来源编码
                        ,order_source_name        --订单来源名称
                        ,scan_site_code           --当前站点编码
                        ,scan_site_name           --当前站点名称
                        ,type
                        ,dt
            ) a group by order_source_code        --订单来源编码
                        ,order_source_name        --订单来源名称
                        ,scan_site_code           --当前站点编码
                        ,scan_site_name           --当前站点名称
                        ,type
        ) peak on  peak.order_source_code        = base.order_source_code        --订单来源编码
              and  peak.order_source_name        = base.order_source_name        --订单来源名称
              and  peak.scan_site_code           = base.scan_site_code           --当前站点编码
              and  peak.scan_site_name           = base.scan_site_name           --当前站点名称
              and  peak.type                     = base.type                     --当前站点名称
    ) a
) a
distribute by dt,pmod(hash(rand()),5);




insert overwrite table jms_dm.dm_waybill_fayun_late_summary_dt
select
     order_source_code        --订单来源编码
    ,order_source_name        --订单来源名称
    ,scan_site_code           --当前站点编码
    ,scan_site_name           --当前站点名称
    ,scan_site_city_code      --当前站点所属城市编码
    ,scan_site_city_name      --当前站点所属城市名称
    ,scan_site_provider_code  --当前站点所属省份编码
    ,scan_site_provider_name  --当前站点所属省份名称
    ,scan_site_agent_code     --当前站点所属代理区编码
    ,scan_site_agent_name     --当前站点所属代理区名称
    ,late_type                --超时环节
    ,duty_type                --异常类别
    ,duty_level               --异常等级
    ,scale_level              --规模等级
    ,today_score              --今日积分
    ,total_score              --累积积分
    ,case when total_score >= 0  and total_score < 5 then '正常'
          when total_score >= 5  and total_score < 10 then '预警'
          when total_score >= 10 and total_score < 20 then '限制'
          when total_score >= 20 then '清退'
          end as control_states           --管控状态
    ,oper_cnt                 --应操作量
    ,duty_cnt                 --异常量
    ,peak_cnt                 --峰值件量
    ,ydlf_36h_cnt             --有到漏发超36h量
    ,yfwx_36h_cnt             --有发未卸超36h量
    ,dt
from (
    select
         a.order_source_code           --订单来源编码
        ,a.order_source_name           --订单来源名称
        ,a.scan_site_code              --当前站点编码
        ,a.scan_site_name              --当前站点名称
        ,a.scan_site_city_code         --当前站点所属城市编码
        ,a.scan_site_city_name         --当前站点所属城市名称
        ,a.scan_site_provider_code     --当前站点所属省份编码
        ,a.scan_site_provider_name     --当前站点所属省份名称
        ,a.scan_site_agent_code        --当前站点所属代理区编码
        ,a.scan_site_agent_name        --当前站点所属代理区名称
        ,a.late_type                   --超时环节
        ,a.duty_type                   --异常类别
        ,a.duty_level                  --异常等级
        ,a.scale_level                 --规模等级
        ,a.today_score                 --今日积分
        ,b.total_score                 --累积积分
        ,a.oper_cnt                    --应操作量
        ,a.duty_cnt                    --异常量
        ,a.peak_cnt                    --峰值件量
        ,a.ydlf_36h_cnt                --有到漏发超36h量
        ,a.yfwx_36h_cnt                --有发未卸超36h量
        ,a.dt
    from (
        select *
        from jms_dm.dm_waybill_fayun_late_mid_dt
        where dt = '{{ execution_date | cst_ds }}'
    ) a
    left join (
        select
             order_source_code           --订单来源编码
            ,order_source_name           --订单来源名称
            ,scan_site_code              --当前站点编码
            ,scan_site_name              --当前站点名称
            ,scan_site_city_code         --当前站点所属城市编码
            ,scan_site_city_name         --当前站点所属城市名称
            ,scan_site_provider_code     --当前站点所属省份编码
            ,scan_site_provider_name     --当前站点所属省份名称
            ,scan_site_agent_code        --当前站点所属代理区编码
            ,scan_site_agent_name        --当前站点所属代理区名称
            ,late_type                   --超时环节
            ,duty_type                   --异常类别
            ,sum(today_score) as total_score --累积积分
        from jms_dm.dm_waybill_fayun_late_mid_dt
        where dt between date_sub('{{ execution_date | cst_ds }}',14) and '{{ execution_date | cst_ds }}'
        group by order_source_code           --订单来源编码
                ,order_source_name           --订单来源名称
                ,scan_site_code              --当前站点编码
                ,scan_site_name              --当前站点名称
                ,scan_site_city_code         --当前站点所属城市编码
                ,scan_site_city_name         --当前站点所属城市名称
                ,scan_site_provider_code     --当前站点所属省份编码
                ,scan_site_provider_name     --当前站点所属省份名称
                ,scan_site_agent_code        --当前站点所属代理区编码
                ,scan_site_agent_name        --当前站点所属代理区名称
                ,late_type                   --超时环节
                ,duty_type                   --异常类别
    ) b on a.order_source_code        = b.order_source_code
       and a.order_source_name        = b.order_source_name
       and a.scan_site_code           = b.scan_site_code
       and a.scan_site_name           = b.scan_site_name
       and a.late_type                = b.late_type
       and a.duty_type                = b.duty_type
) a
distribute by dt,pmod(hash(rand()),5);


